package cz.gillan.housecosts.sql;

import java.util.Date;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import cz.gillan.housecosts.data.Gauge;

public class GaugeDbAdapter extends AbstractDbAdapter {
	public static final String KEY_ROWID = "_id";
	public static final String KEY_TYPE = "type";
	public static final String KEY_NAME = "name";
	public static final String KEY_SERIALNUMBER = "serialNumber";
	public static final String KEY_COMMENT = "comment";
	public static final String KEY_MAGNITUDE = "magnitude";
	public static final String KEY_FIRST_STATE = "firstState";
	public static final String KEY_LAST_STATE = "lastState";
	public static final String KEY_FIRST_STATE_DATE = "firstState";
	public static final String KEY_LAST_STATE_DATE = "lastState";
	public static final String KEY_STATESCOUNT = "statesCount";
	public static final String DB_TABLE = "gauge";
	private static final String[] ALL_COLUMNS = new String[] {
		KEY_ROWID, KEY_TYPE, KEY_NAME, KEY_SERIALNUMBER, KEY_COMMENT,
		KEY_MAGNITUDE };
	private static final String[] CONSUMPTION_COLUMNS = new String[] {
		"MIN(" + StateDbAdapter.DB_TABLE + "." + StateDbAdapter.KEY_STATE + ") AS " + KEY_FIRST_STATE,
		"MAX(" + StateDbAdapter.DB_TABLE + "." + StateDbAdapter.KEY_STATE + ") AS " + KEY_LAST_STATE,
		"COUNT(*) AS " + KEY_STATESCOUNT};
	/*
	private static final String ALL_COLUMNS_WITH_CONSUMPTION = 
		DB_TABLE + "." + KEY_ROWID + ", " + 
		DB_TABLE + "." + KEY_TYPE + ", " +
		DB_TABLE + "." + KEY_NAME + ", " +
		DB_TABLE + "." + KEY_SERIALNUMBER + ", " +
		DB_TABLE + "." + KEY_COMMENT + ", " +
		DB_TABLE + "." + KEY_MAGNITUDE + ", " +
		"MIN(" + StateDbAdapter.DB_TABLE + "." + StateDbAdapter.KEY_STATE + ") AS " + KEY_FIRST_STATE + ", " +
		"MAX(" + StateDbAdapter.DB_TABLE + "." + StateDbAdapter.KEY_STATE + ") AS " + KEY_LAST_STATE;
	*/
	public GaugeDbAdapter(Context context) {
		super(context);
	}

	public boolean createNewGauge(Gauge gauge) {
		ContentValues initialValues = createContentValues(gauge);
		boolean flag = false;
		Long id = getDatabase().insert(DB_TABLE, null, initialValues);
		if (id != null && id > 0) {
			gauge.setId(id);
			flag = true;
		}
		return flag;
	}

	public boolean updateGauge(long rowId, Gauge gauge) {
		ContentValues newValuew = createContentValues(gauge);
		return getDatabase().update(DB_TABLE, newValuew, KEY_ROWID + "=" + rowId, null) > 0;
	}

	public boolean deleteGauge(Gauge gauge) {
		return getDatabase().delete(DB_TABLE, KEY_ROWID + "=" + gauge.getId(), null) > 0;
	}

	public Cursor fetchGauge(long rowId) {
		Cursor cursor = getDatabase().query(true, DB_TABLE, ALL_COLUMNS, KEY_ROWID + "=" + rowId, null, null, null,
				null, null);
		if (cursor != null) {
			cursor.moveToFirst();
		}
		return cursor;
	}
	
	public Cursor fetchAllGauges() {
		return getDatabase().query(DB_TABLE, ALL_COLUMNS, null, null, null, null, null);
	}
	
	public Cursor fetchGaugeConsumptionByDate(long gaugeId, Date from, Date to) {
		Cursor cursor = getDatabase().query(true, StateDbAdapter.DB_TABLE, CONSUMPTION_COLUMNS, 
				StateDbAdapter.KEY_GAUGEID + "=" + gaugeId + " AND " +
				StateDbAdapter.KEY_DATE + " BETWEEN " + from.getTime() + " AND " + to.getTime(),
				null, StateDbAdapter.KEY_GAUGEID, null, null, null);
		
		if (cursor != null) {
			cursor.moveToFirst();
		}
		
		return cursor;
	}
	
	public Cursor fetchGaugeConsumptionByCount(long gaugeId, int count) {
		Cursor cursor = getDatabase().query(true, StateDbAdapter.DB_TABLE, CONSUMPTION_COLUMNS,
				StateDbAdapter.KEY_GAUGEID + "=" + gaugeId, null, StateDbAdapter.KEY_GAUGEID, null, 
				StateDbAdapter.KEY_DATE + " DESC", Integer.toString(count));
		if (cursor != null) {
			cursor.moveToFirst();
		}
		
		return cursor;
	}

	private ContentValues createContentValues(Gauge gauge) {
		ContentValues values = new ContentValues();
		values.put(KEY_TYPE, 1);
		values.put(KEY_NAME, gauge.getName());
		values.put(KEY_SERIALNUMBER, gauge.getSerialNumber());
		values.put(KEY_COMMENT, gauge.getComment());
		//values.put(KEY_MAGNITUDE, gauge.getMagnitude());
		values.put(KEY_MAGNITUDE, "m^3");

		return values;
	}

}
